package com.luych.cloud.database.comparetor.dm;

import com.luych.cloud.database.comparetor.core.Column;
import com.luych.cloud.database.comparetor.core.Connection;
import com.luych.cloud.database.comparetor.core.Database;
import com.luych.cloud.database.comparetor.core.Table;
import dm.jdbc.driver.DmDriver;
import dm.jdbc.driver.DmdbDataSource;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.jdbc.core.JdbcTemplate;

import javax.sql.DataSource;
import java.util.*;
import java.util.stream.Collectors;

public class DmConnection implements Connection {

    private static Logger LOGGER = LoggerFactory.getLogger(DmConnection.class);

    private JdbcTemplate jdbcTemplate;

    public DmConnection(String url, String username, String password) {
        DataSource dataSource = DataSourceBuilder.create()
                .type(DmdbDataSource.class)
                .driverClassName(DmDriver.class.getName())
                .url(url).username(username).password(password)
                .build();
        this.jdbcTemplate = new JdbcTemplate(dataSource);
    }

    private String getType(String dataType) {
        try {
            DmDataType.valueOf(dataType);
        } catch (IllegalArgumentException e) {
            LOGGER.error("Unknown Data Type: " + dataType);
        }
        return dataType;
    }

    private String getColumnAlias(String columnType, String columnCharLength, String columnNumericPrecision, String columnNumericScale) {
        if (DmDataType.CHAR.name().equalsIgnoreCase(columnType) || DmDataType.VARCHAR.name().equalsIgnoreCase(columnType)) {
            return columnType + "(" + columnCharLength + ")";
        } else if (DmDataType.REAL.name().equalsIgnoreCase(columnType) ||
                DmDataType.FLOAT.name().equalsIgnoreCase(columnType) ||
                DmDataType.DOUBLE.name().equalsIgnoreCase(columnType) ||
                DmDataType.DEC.name().equalsIgnoreCase(columnType) ||
                DmDataType.DECIMAL.name().equalsIgnoreCase(columnType) ||
                DmDataType.NUMERIC.name().equalsIgnoreCase(columnType)) {
            return columnType + "(" + columnNumericPrecision + ", " + columnNumericScale + ")";
        } else {
            return columnType;
        }
    }

    private List<Table> getTables(String database) {
        LOGGER.info("Get [" + database + "] Tables ...");
        List<Map<String, Object>> tableMaps = jdbcTemplate.queryForList(
                "SELECT A.TABLE_NAME, B.COMMENTS " +
                "FROM DBA_TABLES A LEFT JOIN DBA_TAB_COMMENTS B " +
                "ON A.TABLE_NAME = B.TABLE_NAME AND A.OWNER = B.OWNER " +
                "WHERE A.TABLESPACE_NAME = '" + database + "' "
        );
        LOGGER.info("Get [" + database + "] Columns ...");
        List<Map<String, Object>> columnMaps = jdbcTemplate.queryForList(
                "SELECT A.TABLE_NAME, A.COLUMN_NAME, A.DATA_TYPE, '' AS COLUMN_TYPE, A.NULLABLE, B.COMMENTS, " +
                "A.DATA_LENGTH, A.DATA_PRECISION, A.DATA_SCALE FROM DBA_TAB_COLUMNS A " +
                "LEFT JOIN DBA_COL_COMMENTS B ON A.COLUMN_NAME = B.COLUMN_NAME AND A.TABLE_NAME = B.TABLE_NAME AND A.OWNER = B.OWNER " +
                "INNER JOIN DBA_TABLES C ON A.TABLE_NAME = C.TABLE_NAME AND A.OWNER = C.OWNER " +
                "WHERE C.TABLESPACE_NAME = '" + database + "' "
        );
        LOGGER.info("Get [" + database + "] PrimaryKeys ...");
        List<Map<String, Object>> primaryKeyMaps = jdbcTemplate.queryForList(
                "SELECT A.TABLE_NAME, B.COLUMN_NAME, B.POSITION " +
                "FROM DBA_CONSTRAINTS A JOIN DBA_CONS_COLUMNS B " +
                "ON A.OWNER = B.OWNER AND A.CONSTRAINT_NAME = B.CONSTRAINT_NAME AND A.TABLE_NAME = B.TABLE_NAME\n" +
                "WHERE A.OWNER = '" + database + "' AND A.CONSTRAINT_TYPE = 'P' " +
                "ORDER BY A.TABLE_NAME, B.POSITION;"
        );

        List<Table> tables = new ArrayList<>();
        for (Map<String, Object> tableMap : tableMaps) {
            String tableName = Objects.toString(tableMap.get("TABLE_NAME"), "");
            String tableComment = Objects.toString(tableMap.get("COMMENTS"), "");
            List<Column> columns = new ArrayList<>();
            for (Map<String, Object> columnMap : columnMaps) {
                if (tableName.equals(Objects.toString(columnMap.get("TABLE_NAME"), ""))) {
                    String columnName = Objects.toString(columnMap.get("COLUMN_NAME"), "");
                    String columnDataType = Objects.toString(columnMap.get("DATA_TYPE"), "").toUpperCase();
                    String columnNullable = Objects.toString(columnMap.get("NULLABLE"), "");
                    String columnComment = Objects.toString(columnMap.get("COMMENTS"), "");
                    String columnCharLength = Objects.toString(columnMap.get("DATA_LENGTH"), "0");
                    String columnNumericPrecision = Objects.toString(columnMap.get("DATA_PRECISION"), "0");
                    String columnNumericScale = Objects.toString(columnMap.get("DATA_SCALE"), "0");

                    if (!DmDataType.CHAR.name().equalsIgnoreCase(columnDataType) && !DmDataType.VARCHAR.name().equalsIgnoreCase(columnDataType)) {
                        columnCharLength = "0";
                    }
                    if (!DmDataType.REAL.name().equalsIgnoreCase(columnDataType) &&
                            !DmDataType.FLOAT.name().equalsIgnoreCase(columnDataType) &&
                            !DmDataType.DOUBLE.name().equalsIgnoreCase(columnDataType) &&
                            !DmDataType.DEC.name().equalsIgnoreCase(columnDataType) &&
                            !DmDataType.DECIMAL.name().equalsIgnoreCase(columnDataType) &&
                            !DmDataType.NUMERIC.name().equalsIgnoreCase(columnDataType)) {
                        columnNumericPrecision = "0";
                        columnNumericScale = "0";
                    }

                    String columnColumnType = getColumnAlias(columnDataType, columnCharLength, columnNumericPrecision, columnNumericScale);

                    Column column = new DmColumn(columnName, getType(columnDataType), columnColumnType,
                            Long.parseLong(columnCharLength),
                            Long.parseLong(columnNumericPrecision),
                            Long.parseLong(columnNumericScale),
                            "Y".equals(columnNullable),
                            columnComment
                    );
                    columns.add(column);
                }
            }
            List<String> primaryKeys = new ArrayList<>();
            for (Map<String, Object> primaryKeyMap : primaryKeyMaps) {
                if (tableName.equals(Objects.toString(primaryKeyMap.get("TABLE_NAME"), ""))) {
                    primaryKeys.add(Objects.toString(primaryKeyMap.get("COLUMN_NAME"), ""));
                }
            }
            Table table = new DmTable(tableName, tableComment, columns, primaryKeys);
            tables.add(table);
        }
        return tables;
    }

    @Override
    public Database getDatabase(String name) {
        return new DmDatabase(name, this.getTables(name));
    }

    @Override
    public List<Database> getDatabases() {
        LOGGER.info("Get Databases ...");
        List<String> databaseNames = getDatabaseNames();
        List<Database> databases = new ArrayList<>();
        for (String databaseName : databaseNames) {
            databases.add(new DmDatabase(databaseName, getTables(databaseName)));
        }
        return databases;
    }

    @Override
    public List<String> getDatabaseNames() {
        List<String> databaseNames = jdbcTemplate.queryForList("SELECT TABLESPACE_NAME FROM DBA_TABLESPACES", String.class);
        databaseNames.sort(String::compareTo);
        return databaseNames;
    }

    @Override
    public List<String> getDataTypes() {
        return Arrays.stream(DmDataType.values()).map(Enum::name).collect(Collectors.toList());
    }

    @Override
    public void execute(String database, String sql) {
        jdbcTemplate.execute(sql);
    }
}
